package org.example;

import java.sql.*;

public class CallableStatementTest {
    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String user = "root";
    private static final String password = "123456";
    private static Connection conn = null;

    public static void callProcedureWithOutParams(int teacherId) {
        try {
            // 使用CallableStatement调用存储过程
            String sql = "{CALL getTeacherNameAndTrsById(?, ?, ?)}";
            CallableStatement cstmt = conn.prepareCall(sql);

            // 设置输入参数
            cstmt.setInt(1, teacherId);

            // 注册输出参数
            cstmt.registerOutParameter(2, Types.VARCHAR);
            cstmt.registerOutParameter(3, Types.VARCHAR);

            // 执行存储过程
            cstmt.execute();

            // 获取输出参数值
            String name = cstmt.getString(2);
            String trs = cstmt.getString(3);

            System.out.println("教师ID: " + teacherId);
            System.out.println("姓名: " + name);
            System.out.println("教研室: " + trs);

            cstmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 调用存储过程 - 使用结果集版本（推荐）
     */
    public static void callProcedureWithResultSet(int teacherId) {
        try {
            String sql = "{CALL getTeacherNameAndTrsById(?)}";
            CallableStatement cstmt = conn.prepareCall(sql);

            // 设置输入参数
            cstmt.setInt(1, teacherId);

            // 执行查询并获取结果集
            ResultSet rs = cstmt.executeQuery();

            if (rs.next()) {
                String name = rs.getString("name");
                String trs = rs.getString("trs");

                System.out.println("教师ID: " + teacherId);
                System.out.println("姓名: " + name);
                System.out.println("教研室: " + trs);
            } else {
                System.out.println("未找到ID为 " + teacherId + " 的教师");
            }

            rs.close();
            cstmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试存储过程
     */
    public static void testStoredProcedure() {
        System.out.println("=== 测试存储过程 getTeacherNameAndTrsById ===");

        // 测试存在的ID
        System.out.println("\n1. 测试存在的教师ID:");
        callProcedureWithResultSet(1);

        // 测试不存在的ID
        System.out.println("\n2. 测试不存在的教师ID:");
        callProcedureWithResultSet(999);

        // 测试多个ID
        System.out.println("\n3. 测试多个教师ID:");
        callProcedureWithResultSet(2);
        callProcedureWithResultSet(3);
    }

    public static void main(String[] args) {
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("数据库连接成功");

            // 测试存储过程
            testStoredProcedure();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("\n数据库连接已关闭");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}